package mailsystem.persistance.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import mailsystem.Mailbox;
import mailsystem.persistance.ContactPersistance;
import mailsystem.persistance.MailsystemPersistance;

public class ContactDB extends MailSystemDB implements ContactPersistance {

	public ContactDB(MailsystemPersistance repo) {
		super(repo);		
	}

	@Override
	public void add(Mailbox box, Mailbox contact) {		
		String sql = "INSERT INTO Contact (boxId, contactId) VALUES (?, ?)";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try
		{
			statement.setInt(1, box.getBoxId());
			statement.setInt(2, contact.getBoxId());
			
			statement.executeUpdate();
		}
		catch (SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
	}

	@Override
	public void delete(Mailbox box, Mailbox contact) {
		String sql = "DELETE FROM Contact WHERE boxId = ? and contactId = ?";
		PreparedStatement statement = connection.prepareStatement(sql);
		
		try
		{
			statement.setInt(1, box.getBoxId());
			statement.setInt(2, contact.getBoxId());
			
			statement.executeUpdate();
		}
		catch (SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}

	}

	@Override
	public List<Mailbox> listAll(Mailbox box) {
		String sql = "SELECT MailBox.boxId, passcode, greeting, name " +
						"FROM Contact, MailBox WHERE " + 
						"Contact.boxId = ? and Contact.contactId = MailBox.boxId";
		PreparedStatement statement = connection.prepareStatement(sql);
		ArrayList<Mailbox> resp = new ArrayList<Mailbox>();
		
		try
		{
			statement.setInt(1, box.getBoxId());
			
			ResultSet result = statement.executeQuery();
			
			while (result.next())
			{
				resp.add( new Mailbox(
						result.getInt("boxId"),
						result.getString("passcode"), 
						result.getString("greeting"),						
						result.getString("name"), repository));
			}
		}
		catch(SQLException sqlException)
		{
			sqlException.printStackTrace();
		}
		finally
		{
			try {
				statement.close();				
			} catch (SQLException sqlException2) {
				sqlException2.printStackTrace();
			}
		}
		return resp;
	}

}
